﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// Summary description for cls_tbHOCVIEN
/// </summary>
public class cls_tbHOCVIEN
{
	public cls_tbHOCVIEN()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    #region Function

    //#region Insert

    //public static string AddAdmin(string username, string password, string notes, int Id_Create, int Limit)
    //{
    //    av.dal.SqlDataProvider clsSQL = new av.dal.SqlDataProvider();
    //    object[] myValue = { username, password, notes, Id_Create, Limit };
    //    string[] myPara = { "@Name", "@Password", "@Notes", "@Id_Create", "@Limit" };
    //    SqlParameter[] Parameter = new SqlParameter[myPara.Length];
    //    for (int i = 0; i < myPara.Length; i++)
    //    {
    //        Parameter[i] = new SqlParameter(myPara[i], myValue[i]);
    //    }
    //    try
    //    {
    //        if (clsSQL.ExecuteNonQuery("ADMIN_Insert", CommandType.StoredProcedure, Parameter) > 0)
    //            return "OK";
    //        else
    //            return "NO";
    //    }
    //    catch (SqlException ex) { return ex.Message; }
    //}

    //#endregion

    //#region Delete

    //public static string Delete(Int32 id, int Id_Delete)
    //{
    //    av.dal.SqlDataProvider clsSQL = new av.dal.SqlDataProvider();
    //    SqlParameter[] parameter = new SqlParameter[2];
    //    parameter[0] = new SqlParameter("@Id_Admin", id);
    //    parameter[1] = new SqlParameter("@Id_Delete", Id_Delete);
    //    try
    //    {
    //        if (clsSQL.ExecuteNonQuery("ADMIN_Delete", CommandType.StoredProcedure, parameter) > 0)
    //            return "OK";
    //        else return "NO";
    //    }
    //    catch (Exception ex) { return ex.Message; }
    //}

    //#endregion

    //#region Update

    //public static string Update(int id_admin, string username, string password, string notes)
    //{
    //    av.dal.SqlDataProvider clsSQL = new av.dal.SqlDataProvider();
    //    object[] myValue = { id_admin, username, password, notes };
    //    string[] myPara = { "@Id_Admin", "@Name", "@Password", "@Notes" };
    //    SqlParameter[] Parameter = new SqlParameter[myPara.Length];
    //    for (int i = 0; i < myPara.Length; i++)
    //    {
    //        Parameter[i] = new SqlParameter(myPara[i], myValue[i]);
    //    }
    //    try
    //    {
    //        if (clsSQL.ExecuteNonQuery("ADMIN_Update", CommandType.StoredProcedure, Parameter) > 0)
    //            return "OK";
    //        else return "NO";
    //    }
    //    catch (Exception ex) { return ex.Message; }
    //}

    //public static string UpdateUser(int id_admin, string username, string notes, int Limit)
    //{
    //    av.dal.SqlDataProvider clsSQL = new av.dal.SqlDataProvider();
    //    object[] myValue = { id_admin, username, notes, Limit };
    //    string[] myPara = { "@Id_Admin", "@Name", "@Notes", "@Limit" };
    //    SqlParameter[] Parameter = new SqlParameter[myPara.Length];
    //    for (int i = 0; i < myPara.Length; i++)
    //    {
    //        Parameter[i] = new SqlParameter(myPara[i], myValue[i]);
    //    }
    //    try
    //    {
    //        if (clsSQL.ExecuteNonQuery("ADMIN_UpdateUser", CommandType.StoredProcedure, Parameter) > 0)
    //            return "OK";
    //        else return "NO";
    //    }
    //    catch (Exception ex) { return ex.Message; }
    //}
    //#endregion

    //#region CheckAdmin

    ///// <summary>
    ///// Ham kiem tra tai khoan admin
    ///// </summary>
    ///// <param name="username">Ten tai khoan dang nhap</param>
    ///// <param name="password">Mat khau dang nhap</param>
    ///// <returns></returns>
    //public static bool CheckAdmin(string username, string password)
    //{
    //    av.dal.SqlDataProvider clsSQL = new av.dal.SqlDataProvider();
    //    object[] myValue = { username, password };
    //    string[] myPara = { "@Name", "@Password" };
    //    SqlParameter[] Parameter = new SqlParameter[myPara.Length];
    //    DataTable table = new DataTable();
    //    for (int i = 0; i < myPara.Length; i++)
    //    {
    //        Parameter[i] = new SqlParameter(myPara[i], myValue[i]);
    //    }
    //    try
    //    {
    //        table = clsSQL.GetDataFrom("ADMIN_CheckAccount", CommandType.StoredProcedure, Parameter);
    //        if (table.Rows.Count > 0)
    //            return true;
    //        else return false;
    //    }
    //    catch (Exception ex) { return false; }
    //}

    //public static bool CheckUser(string username)
    //{
    //    av.dal.SqlDataProvider clsSQL = new av.dal.SqlDataProvider();
    //    DataTable table = new DataTable();
    //    SqlParameter[] Parameter = new SqlParameter[1];
    //    Parameter[0] = new SqlParameter("@Name", username);
    //    try
    //    {
    //        table = clsSQL.GetDataFrom("ADMIN_CheckUser", CommandType.StoredProcedure, Parameter);
    //        if (table.Rows.Count > 0)
    //            return true;
    //        else return false;
    //    }
    //    catch (Exception ex) { return false; }
    //}
    //public static DataTable GetByUser(string username)
    //{
    //    av.dal.SqlDataProvider clsSQL = new av.dal.SqlDataProvider();
    //    DataTable table = new DataTable();
    //    SqlParameter[] Parameter = new SqlParameter[1];
    //    Parameter[0] = new SqlParameter("@Name", username);
    //    try
    //    {
    //        table = clsSQL.GetDataFrom("ADMIN_CheckUser", CommandType.StoredProcedure, Parameter);
    //    }
    //    catch (Exception ex) { }
    //    return table;
    //}
    ///// <summary>
    ///// Ham tim admin
    ///// </summary>
    ///// <param name="username">Ten admin can tim</param>
    ///// <returns></returns>
    //public static DataTable SearchAdmin(string username, string pass)
    //{
    //    av.dal.SqlDataProvider clsSQL = new av.dal.SqlDataProvider();
    //    DataTable table = new DataTable();
    //    SqlParameter[] parameter = new SqlParameter[2];
    //    parameter[0] = new SqlParameter("@Name", username);
    //    parameter[1] = new SqlParameter("@Password", pass);
    //    try
    //    {
    //        table = clsSQL.GetDataFrom("ADMIN_SearchAdmin", CommandType.StoredProcedure, parameter);
    //    }
    //    catch (Exception ex) { }
    //    return table;
    //}
    //#endregion

    #region Get + Set

    //public static string SetCurrent(string username)
    //{
    //    av.dal.SqlDataProvider clsSQL = new av.dal.SqlDataProvider();
    //    DataTable table = new DataTable();
    //    SqlParameter[] Parameter = new SqlParameter[1];
    //    Parameter[0] = new SqlParameter("@Name", username);
    //    try
    //    {
    //        if (clsSQL.ExecuteNonQuery("ADMIN_SetCurrent", CommandType.StoredProcedure, Parameter) > 0)
    //            return "OK";
    //        else return "NO";
    //    }
    //    catch (Exception ex) { return ex.ToString(); }
    //}
    //public static DataTable GetCurrent()
    //{
    //    av.dal.SqlDataProvider clsSQL = new av.dal.SqlDataProvider();
    //    DataTable table = new DataTable();
    //    try
    //    {
    //        table = clsSQL.GetDataFrom("ADMIN_GetCurrent", CommandType.StoredProcedure, null);
    //    }
    //    catch (Exception ex) { }
    //    return table;
    //}
    //public static DataTable GetAdmin(int adminID)
    //{
    //    DataTable table = new DataTable();
    //    av.dal.SqlDataProvider clsSQL = new av.dal.SqlDataProvider();
    //    SqlParameter[] parameter = new SqlParameter[1];
    //    parameter[0] = new SqlParameter("@Id_Admin", adminID);
    //    try
    //    {
    //        table = clsSQL.GetDataFrom("ADMIN_GetAdmin", CommandType.StoredProcedure, parameter);
    //    }
    //    catch (Exception ex) { }
    //    return table;
    //}        //Id_admin = 0 lay tat ca
    //public static DataTable Get_manv_hoten_NHANVIEN()
    //{
    //    DataTable table = new DataTable();
    //    av.dal.SqlDataProvider clsSQL = new av.dal.SqlDataProvider();
    //    //SqlParameter[] parameter = new SqlParameter[1];
    //    //parameter[0] = new SqlParameter("@Id_Admin", adminID);
    //    try
    //    {
    //        //table = clsSQL.GetDataFrom("ADMIN_GetAllAdmin", CommandType.StoredProcedure, null);
    //        table = clsSQL.GetDataFrom("select F_MANV,F_HOTENNV from tbNHANVIEN", CommandType.Text, null);

    //    }
    //    catch (Exception ex) { }
    //    return table;
    //}
    //public static DataTable GetAll_NHANVIEN()
    //{
    //    DataTable table = new DataTable();
    //    av.dal.SqlDataProvider clsSQL = new av.dal.SqlDataProvider();
    //    //SqlParameter[] parameter = new SqlParameter[1];
    //    //parameter[0] = new SqlParameter("@Id_Admin", adminID);
    //    try
    //    {
    //        //table = clsSQL.GetDataFrom("ADMIN_GetAllAdmin", CommandType.StoredProcedure, null);
    //        table = clsSQL.GetDataFrom("select * from tbNHANVIEN", CommandType.Text, null);

    //    }
    //    catch (Exception ex) { }
    //    return table;
    //}
    //public static DataTable GetAll_NHANVIEN_QUYEN()
    //{
    //    DataTable table = new DataTable();
    //    av.dal.SqlDataProvider clsSQL = new av.dal.SqlDataProvider();
    //    //SqlParameter[] parameter = new SqlParameter[1];
    //    //parameter[0] = new SqlParameter("@Id_Admin", adminID);
    //    try
    //    {
    //        //table = clsSQL.GetDataFrom("ADMIN_GetAllAdmin", CommandType.StoredProcedure, null);
    //        table = clsSQL.GetDataFrom("select * from tbNHANVIEN a, tbQUYEN b where a.F_STTQUYEN=b.F_STTQUYEN", CommandType.Text, null);

    //    }
    //    catch (Exception ex) { }
    //    return table;
    //}
    public static DataTable Get_HOCVIEN(string F_HOTEN, string F_SODT)
    {
        DataTable table = new DataTable();
        av.dal.SqlDataProvider clsSQL = new av.dal.SqlDataProvider();
        SqlParameter[] parameter = new SqlParameter[2];
        parameter[0] = new SqlParameter("@F_HOTEN", F_HOTEN);
        parameter[1] = new SqlParameter("@F_SODT", F_SODT);
        try
        {
            table = clsSQL.GetDataFrom("select * from tbHOCVIEN where [F_HOTEN] like '%" + F_HOTEN + "%' --and upper(RTRIM(b.F_SODT)) like '%" + F_SODT + "%'", CommandType.Text, null/*parameter*/);
        }
        catch { }
        return table;
    }        //Id_admin = 0 lay tat ca
    #endregion

    #endregion

    #region Get List By Lop Va Chi Nhanh
    public static DataTable GetListByLopChiNhanh(string F_MALOP, string F_MACN)
    {
        DataTable table = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        SqlParameter[] para = new SqlParameter[2];
        para[0] = new SqlParameter("@F_MALOP",F_MALOP);
        para[1] = new SqlParameter("@F_MACN", F_MACN);
        try
        {
            table = connect.GetSelectCommand("HOCVIEN_GetListByLop_ChiNhanh", para);
        }
        catch
        { }
        return table;
    }
    public static DataTable GetListHocVienLopChuyenToiByLopChiNhanh(string F_MALOP, string F_MACN)
    {
        DataTable table = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        SqlParameter[] para = new SqlParameter[2];
        para[0] = new SqlParameter("@F_MALOP", F_MALOP);
        para[1] = new SqlParameter("@F_MACN", F_MACN);
        try
        {
            table = connect.GetSelectCommand("HOCVIEN_GetListHocVienLopChuyenToiByLop_ChiNhanh", para);
        }
        catch
        { }
        return table;
    }
    #endregion

    #region Get No Phi By Lop Va Ma hoc vien
    public static DataTable GetDetail_NoPhiByLopMaHV(string F_MALOP, string F_MAHV)
    {
        DataTable table = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        SqlParameter[] para = new SqlParameter[2];
        para[0] = new SqlParameter("@F_MALOP", F_MALOP);
        para[1] = new SqlParameter("@F_MAHV", F_MAHV);
        try
        {
            table = connect.GetSelectCommand("NOPHI_GetDetail_NoPhiByLop_MaHV", para);
        }
        catch
        { }
        return table;
    }
    #endregion

    #region Get List By Dot Thu
    public static DataTable GetListByDotThu ( string F_MADT)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        SqlParameter[] para = new SqlParameter[1];
        para[0] = new SqlParameter("@F_MADT", F_MADT);
        try
        {
            dt = connect.GetSelectCommand("HOCVIEN_GetListByMaDotThu", para);
        }
        catch
        {}
        return dt;
    }
    #endregion

    #region Get By Ma Hoc Vien
    public static DataTable GetByMaHV(string F_MAHV)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        SqlParameter[] para = new SqlParameter[1];
        para[0] = new SqlParameter("@F_MAHV", F_MAHV);
        try
        {
            dt = connect.GetSelectCommand("HOCVIEN_GetByMaHV", para);
        }
        catch
        { }
        return dt;
    }
    #endregion

    #region Kiem tra ma hoc vien ton tai
    public static bool Exist_MaHV(string F_MAHV)
    {
        bool result = false;
        DataTable dt = new DataTable();
        dt = GetByMaHV(F_MAHV);
        if (dt.Rows.Count == 1)
        {
            result = true;
        }
        return result;
    }
    #endregion


    #region Insert
    public static string Insert(string F_MAHV, string F_HOTEN, string F_NGAYSINH, string F_GIOITINH, string F_DIACHI, string F_SODT, bool F_DANGHI, string F_GHICHU, DateTime F_NGAYNHAPHOC, string F_TENCONGTY, string F_DIACHICONGTY, string F_MASOTHUE, string F_SOTAIKHOAN, string F_MALOP)
    {
        string Result = "";
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so Hoc Vien
        object[] values = { F_MAHV, F_HOTEN, F_NGAYSINH, F_GIOITINH, F_DIACHI, F_SODT, F_DANGHI, F_GHICHU, F_NGAYNHAPHOC, F_TENCONGTY, F_DIACHICONGTY, F_MASOTHUE, F_SOTAIKHOAN };
        string[] parameters = { "@F_MAHV", "@F_HOTEN", "@F_NGAYSINH", "@F_GIOITINH", "@F_DIACHI", "@F_SODT", "@F_DANGHI", "@F_GHICHU", "@F_NGAYNHAPHOC", "@F_TENCONGTY", "@F_DIACHICONGTY", "@F_MASOTHUE", "@F_SOTAIKHOAN" };

        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        // Tham so Hoc Lop
        SqlParameter[] para1 = new SqlParameter[3];
        para1[0] = new SqlParameter("@F_MAHV", F_MAHV);
        para1[1] = new SqlParameter("@F_MALOP", F_MALOP);
        para1[2] = new SqlParameter("@F_THANGTHU", 1);

        try
        {
            connect.ExecuteQuery("HOCVIEN_Insert", para);
            connect.ExecuteQuery("HOCLOP_Insert", para1);
            Result = "OK";
        }
        catch
        {
            Result = "ERR";
        }

        // Insert Nophi
        DataTable dt = clsDOTTHU.GetLastRowByMaLop(F_MALOP);
        string F_MADT;
        decimal F_TIENNO;
        if (dt.Rows.Count > 0)
        {
            F_MADT = dt.Rows[0]["F_MADT"].ToString();
            F_TIENNO = decimal.Parse(dt.Rows[0]["F_SOTIENTHU"].ToString());
            // Tham so
            SqlParameter[] para2 = new SqlParameter[3];
            para2[0] = new SqlParameter("@F_MAHV", F_MAHV);
            para2[1] = new SqlParameter("@F_MADT", F_MADT);
            para2[2] = new SqlParameter("@F_TIENNO", F_TIENNO);
            try
            {
                connect.ExecuteQuery("NOPHI_Insert", para2);
            }
            catch
            { }
        }

        // Insert điểm danh
        string F_MADD = clsDIEMDANH.GetLastId();
        if (F_MADD != string.Empty)
        {
            Guid Id = Guid.Parse(F_MADD);
            SqlParameter[] para3 = new SqlParameter[3];
            para3[0] = new SqlParameter("@F_MADD", Id);
            para3[1] = new SqlParameter("@F_MAHV", F_MAHV);
            para3[2] = new SqlParameter("@F_MALOP", F_MALOP);
            try
            {
                connect.ExecuteQuery("DIEMDANH_CHITIET_Insert", para3);
                //Result = "OK";
            }
            catch {
                //Result = "ERR";
            }
        }
        return Result;
    }
    #endregion

    #region Update
    public static string Update(string F_MAHV, string F_HOTEN, string F_NGAYSINH, string F_GIOITINH, string F_DIACHI, string F_SODT, bool F_DANGHI, string F_GHICHU, DateTime F_NGAYNHAPHOC, string F_TENCONGTY, string F_DIACHICONGTY, string F_MASOTHUE, string F_SOTAIKHOAN, string F_MALOP)
    {
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { F_MAHV, F_HOTEN, F_NGAYSINH, F_GIOITINH, F_DIACHI, F_SODT, F_DANGHI, F_GHICHU, F_NGAYNHAPHOC, F_TENCONGTY, F_DIACHICONGTY, F_MASOTHUE, F_SOTAIKHOAN, F_MALOP };
        string[] parameters = { "@F_MAHV", "@F_HOTEN", "@F_NGAYSINH", "@F_GIOITINH", "@F_DIACHI", "@F_SODT", "@F_DANGHI", "@F_GHICHU", "@F_NGAYNHAPHOC", "@F_TENCONGTY", "@F_DIACHICONGTY", "@F_MASOTHUE", "@F_SOTAIKHOAN", "@F_MALOP" };

        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            connect.ExecuteQuery("HOCVIEN_Update", para);
            return "OK";
        }
        catch
        {
            return "ERR";
        }
    }
    #endregion

    #region Chuyển lớp
    public static string Change_class(string F_MAHV, string F_MALOP)
    {
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        object[] values = { F_MAHV, F_MALOP };
        string[] parameters = { "@F_MAHV", "@F_MALOP" };

        SqlParameter[] para = new SqlParameter[parameters.Length];
        for (int i = 0; i < parameters.Length; i++)
        {
            para[i] = new SqlParameter(parameters[i], values[i]);
        }

        try
        {
            connect.ExecuteQuery("HOCVIEN_Chuyenlop", para);
            return "OK";
        }
        catch
        {
            return "ERR";
        }
    }
    #endregion

    #region Delete
    public static string Delete(string F_MAHV, string F_MALOP, bool F_KT)
    {
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        SqlParameter[] para = new SqlParameter[3];
        para[0] = new SqlParameter("@F_MAHV", F_MAHV);
        para[1] = new SqlParameter("@F_MALOP", F_MALOP);
        para[2] = new SqlParameter("@F_KT", F_KT);

        try
        {
            connect.ExecuteQuery("HOCVIEN_Delete", para);
            return "OK";
        }
        catch
        {
            return "ERR";
        }
    }
    #endregion

    #region Get Last Row
    public static DataTable GetLastRow(string F_MALOP)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        // Tham so
        SqlParameter[] para = new SqlParameter[1];
        para[0] = new SqlParameter("@F_MALOP", F_MALOP);
        try
        {
            dt = connect.GetSelectCommand("HOCVIEN_GetLastRow", para);
        }
        catch
        {
            dt = null;
        }
        return dt;
    }
    #endregion

    #region Search
    public static DataTable Search(string F_HOTEN, string F_SODT)
    {
        DataTable dt = new DataTable();
        DatabaseConnect connect = new DatabaseConnect();
        SqlParameter[] para = new SqlParameter[2];
        para[0] = new SqlParameter("@HOTEN", F_HOTEN);
        para[1] = new SqlParameter("@DIENTHOAI", F_SODT);
        try
        {
            dt = connect.GetSelectCommand("HOCVIEN_TimKiem", para);
        }
        catch { }
        return dt;
    }
    #endregion
}